import pandas as pd
import numpy as np
import re
import datetime
import sys
from collections import defaultdict

# 设置控制台输出编码为UTF-8
if sys.stdout.encoding != 'utf-8':
    try:
        sys.stdout.reconfigure(encoding='utf-8')
    except AttributeError:
        # Python 3.6及以下版本
        import codecs
        sys.stdout = codecs.getwriter('utf-8')(sys.stdout.buffer)

# 状态映射函数
def map_status(excel_status):
    """
    将Excel中的状态映射到数据库中的标准状态
    同时处理建设方式和子状态
    返回: (status, construction_type, sub_status, notes)
    """
    # 初始化返回值
    db_status = None
    construction_type = None
    sub_status = None
    notes = None
    
    # 处理None或NaN值
    if pd.isna(excel_status) or excel_status is None:
        return "前期-方案设计", construction_type, sub_status, notes
    
    # 处理建设方式标记
    if "（统建）" in excel_status or "(统建)" in excel_status:
        construction_type = "统建"
        excel_status = excel_status.replace("（统建）", "").replace("(统建)", "").strip()
    
    # 处理复合状态 (包含多个地点的不同状态)
    complex_patterns = [
        "初步验收（.*）已移交（.*）",
        "初步验收（.*点位）.*已移交（.*点位）"
    ]
    
    is_complex = False
    for pattern in complex_patterns:
        if re.search(pattern, excel_status):
            is_complex = True
            notes = excel_status  # 保存原始状态作为备注
            db_status = "竣工-实体移交"
            break
    
    if is_complex:
        return db_status, construction_type, sub_status, notes
    
    # 处理简单状态映射
    status_map = {
        "方案设计": "前期-方案设计",
        "施工图设计及清单编制": "前期-施工图设计及清单编制",
        "清单审核": "前期-清单审核",
        "发包组织实施": "发包-发包组织实施",
        "发包方案编制": "发包-发包方案编制",
        "评标结果公示": "发包-评标结果公示",
        "待开工": "施工-待开工",
        "在建": "施工-在建",
        "初步验收": "施工-初步验收",
        "竣工验收": "竣工-竣工验收",
        "竣工未移交": "竣工-竣工验收",
        "已移交": "竣工-实体移交",
        "完成结算": "竣工-完成结算",
        "完成决算": "竣工-完成决算",
        "撤项": "撤项",
        "未下达任务书": "前期-方案设计"
    }
    
    # 处理子状态
    sub_status_map = {
        "竣工未移交": "未移交",
        "未下达任务书": "未下达任务书"
    }
    
    if excel_status in status_map:
        db_status = status_map[excel_status]
        if excel_status in sub_status_map:
            sub_status = sub_status_map[excel_status]
    else:
        # 默认状态
        db_status = "前期-方案设计"
        notes = f"未识别的原始状态: {excel_status}"
    
    return db_status, construction_type, sub_status, notes

# 处理日期函数
def format_date(date_value):
    """
    将各种格式的日期转换为标准的ISO格式 (YYYY-MM-DD)
    处理复杂的日期格式，包括带有括号的附加信息
    """
    if pd.isna(date_value) or date_value is None or date_value == '/' or date_value == '':
        return None
    
    # 如果是数值型日期（Excel日期序列号）
    if isinstance(date_value, (int, float)) and not pd.isna(date_value):
        try:
            # Excel日期序列号从1900年1月1日开始计算
            # 但Python的datetime从1970年1月1日开始，需要转换
            excel_epoch = datetime.datetime(1899, 12, 30)  # Excel的起始日期
            delta = datetime.timedelta(days=int(date_value))
            date_obj = excel_epoch + delta
            return date_obj.strftime('%Y-%m-%d')
        except (ValueError, OverflowError):
            # 如果转换失败，可能不是有效的Excel日期序列号
            pass
    
    # 如果已经是datetime对象
    if isinstance(date_value, (datetime.datetime, pd.Timestamp)):
        return date_value.strftime('%Y-%m-%d')
    
    # 如果是字符串，尝试提取日期部分
    if isinstance(date_value, str):
        # 检查是否是时间段格式（包含"至"或"到"）
        if "至" in date_value or "到" in date_value:
            # 提取时间段的开始时间
            start_date = extract_date_from_period(date_value, is_start=True)
            if start_date:
                return start_date
        
        # 首先尝试提取主要日期（括号外的第一个日期）
        
        # 尝试匹配YYYY-MM-DD格式
        date_match = re.search(r'(\d{4})-(\d{1,2})-(\d{1,2})', date_value)
        if date_match:
            year, month, day = date_match.groups()
            return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
        
        # 尝试匹配YYYY/MM/DD格式
        date_match = re.search(r'(\d{4})/(\d{1,2})/(\d{1,2})', date_value)
        if date_match:
            year, month, day = date_match.groups()
            return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
        
        # 尝试匹配YYYY年MM月DD日格式
        date_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', date_value)
        if date_match:
            year, month, day = date_match.groups()
            return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
        
        # 尝试匹配YYYY年MM月格式（没有具体日期）
        date_match = re.search(r'(\d{4})年(\d{1,2})月', date_value)
        if date_match:
            year, month = date_match.groups()
            return f"{year}-{month.zfill(2)}-01"  # 默认为当月第一天
        
        # 尝试匹配单独的YYYY年格式
        date_match = re.search(r'(\d{4})年', date_value)
        if date_match:
            year = date_match.group(1)
            return f"{year}-01-01"  # 默认为当年第一天
        
        # 尝试匹配"YYYY年MM月DD日收"格式
        date_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日收', date_value)
        if date_match:
            year, month, day = date_match.groups()
            return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
        
        # 如果主要日期没有匹配到，尝试从括号内提取日期
        # 例如："（2018年12月25日移交代建办实施实施）"
        bracket_match = re.search(r'（(.*?)）|\((.*?)\)', date_value)
        if bracket_match:
            bracket_content = bracket_match.group(1) or bracket_match.group(2)
            
            # 从括号内容中提取日期
            # 尝试匹配YYYY年MM月DD日格式
            date_in_bracket = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', bracket_content)
            if date_in_bracket:
                year, month, day = date_in_bracket.groups()
                return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
            
            # 尝试匹配YYYY/MM/DD格式
            date_in_bracket = re.search(r'(\d{4})/(\d{1,2})/(\d{1,2})', bracket_content)
            if date_in_bracket:
                year, month, day = date_in_bracket.groups()
                return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
            
            # 尝试匹配YYYY-MM-DD格式
            date_in_bracket = re.search(r'(\d{4})-(\d{1,2})-(\d{1,2})', bracket_content)
            if date_in_bracket:
                year, month, day = date_in_bracket.groups()
                return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
    
    # 无法解析的情况
    return None

# 从时间段中提取日期
def extract_date_from_period(period_str, is_start=True):
    """
    从时间段字符串中提取开始或结束日期
    例如："2020年4月11日至2020年4月15日" -> "2020-04-11"或"2020-04-15"
    
    参数:
        period_str: 时间段字符串
        is_start: 是否提取开始日期，如果为False则提取结束日期
    
    返回:
        格式化的日期字符串，如果无法提取则返回None
    """
    # 分隔符可能是"至"或"到"
    separators = ["至", "到", "-", "~"]
    separator = None
    
    for sep in separators:
        if sep in period_str:
            separator = sep
            break
    
    if not separator:
        return None
    
    parts = period_str.split(separator)
    if len(parts) < 2:
        return None
    
    target_part = parts[0].strip() if is_start else parts[1].strip()
    
    # 尝试从目标部分提取日期
    # 尝试匹配YYYY年MM月DD日格式
    date_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', target_part)
    if date_match:
        year, month, day = date_match.groups()
        return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
    
    # 尝试匹配YYYY/MM/DD格式
    date_match = re.search(r'(\d{4})/(\d{1,2})/(\d{1,2})', target_part)
    if date_match:
        year, month, day = date_match.groups()
        return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
    
    # 尝试匹配YYYY-MM-DD格式
    date_match = re.search(r'(\d{4})-(\d{1,2})-(\d{1,2})', target_part)
    if date_match:
        year, month, day = date_match.groups()
        return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
    
    # 尝试匹配YYYY年MM月格式
    date_match = re.search(r'(\d{4})年(\d{1,2})月', target_part)
    if date_match:
        year, month = date_match.groups()
        return f"{year}-{month.zfill(2)}-01"
    
    return None

# 处理数值函数
def format_number(value):
    """
    处理数值字段，转换为浮点数或None
    """
    if pd.isna(value) or value is None or value == '/' or value == '':
        return None
    
    try:
        return float(value)
    except (ValueError, TypeError):
        return None

# 转义SQL字符串
def escape_sql_string(s):
    """
    转义SQL字符串中的特殊字符
    """
    if s is None or pd.isna(s):
        return None
    
    # 将nan字符串转换为None
    if isinstance(s, str) and s.lower() == 'nan':
        return None
        
    return str(s).replace("'", "''")

# 生成默认项目名称
def generate_default_project_name(project_number, used_numbers):
    """
    根据项目序号生成默认项目名称
    如果序号重复，则添加后缀
    """
    # 提取项目编号中的数字部分
    if isinstance(project_number, str):
        # 如果是字符串，提取第一个数字部分
        match = re.search(r'(\d+)', project_number)
        if match:
            project_number = int(match.group(1))
        else:
            # 如果没有数字，使用哈希值作为编号
            project_number = abs(hash(project_number)) % 10000
    
    if project_number in used_numbers:
        used_numbers[project_number] += 1
        return f"工程项目{project_number}-{used_numbers[project_number]}"
    else:
        used_numbers[project_number] = 0
        return f"工程项目{project_number}"

# 处理任务书日期的特殊情况
def process_task_dates(df):
    """
    处理任务书下达时间和要求完成时限的特殊情况
    返回处理后的DataFrame
    """
    # 创建一个副本，避免修改原始数据
    df_copy = df.copy()
    
    # 添加调试列，保存原始日期值
    df_copy['original_task_issue_date'] = df_copy['task_issue_date']
    df_copy['original_required_completion_date'] = df_copy['required_completion_date']
    
    # 添加时间段结束日期列（如果需要）
    df_copy['registration_end_date'] = None
    
    # 处理每一行
    for idx, row in df_copy.iterrows():
        # 处理任务书下达时间
        if not pd.isna(row['task_issue_date']):
            formatted_date = format_date(row['task_issue_date'])
            df_copy.at[idx, 'task_issue_date'] = formatted_date
            
            # 如果是时间段，提取结束日期
            if isinstance(row['task_issue_date'], str) and ("至" in row['task_issue_date'] or "到" in row['task_issue_date']):
                end_date = extract_date_from_period(row['task_issue_date'], is_start=False)
                if end_date:
                    df_copy.at[idx, 'registration_end_date'] = end_date
            
        # 处理任务书要求完成时限
        if not pd.isna(row['required_completion_date']):
            formatted_date = format_date(row['required_completion_date'])
            df_copy.at[idx, 'required_completion_date'] = formatted_date
        
        # 处理扩展完成日期
        if 'extended_completion_date' in df_copy.columns:
            if not pd.isna(row.get('extended_completion_date')):
                # 如果是'nan'字符串，设置为None
                if isinstance(row['extended_completion_date'], str) and row['extended_completion_date'].lower() == 'nan':
                    df_copy.at[idx, 'extended_completion_date'] = None
                else:
                    formatted_date = format_date(row['extended_completion_date'])
                    df_copy.at[idx, 'extended_completion_date'] = formatted_date
    
    return df_copy

# 生成SQL插入语句
def generate_insert_sql(df):
    """
    生成SQL插入语句
    """
    sql_statements = []
    
    # 获取当前时间作为created_at和updated_at
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    # 用于跟踪项目序号使用情况
    used_project_numbers = defaultdict(int)
    
    # 遍历有效行
    valid_rows = df[df['project_number'].notna()]
    for _, row in valid_rows.iterrows():
        try:
            # 映射状态
            status, construction_type_from_status, sub_status, status_notes = map_status(row.get('status'))
            
            # 如果construction_type已经有值，优先使用它
            construction_type = row.get('construction_type') if not pd.isna(row.get('construction_type')) else construction_type_from_status
            
            # 处理备注信息
            notes = row.get('notes', '')
            if status_notes:
                notes = f"{notes} {status_notes}".strip()
            
            # 获取已处理的日期字段（不需要再次格式化）
            task_issue_date = row.get('task_issue_date')
            required_completion_date = row.get('required_completion_date')
            extended_completion_date = row.get('extended_completion_date')
            
            # 处理其他日期字段
            scheme_design_confirm_date = format_date(row.get('scheme_design_confirm_date'))
            preliminary_design_confirm_date = format_date(row.get('preliminary_design_confirm_date'))
            construction_drawing_review_date = format_date(row.get('construction_drawing_review_date'))
            bill_compilation_date = format_date(row.get('bill_compilation_date'))
            bill_audit_date = format_date(row.get('bill_audit_date'))
            construction_drawing_confirm_date = format_date(row.get('construction_drawing_confirm_date'))
            bidding_announcement_date = format_date(row.get('bidding_announcement_date'))
            enterprise_registration_date = format_date(row.get('enterprise_registration_date'))
            bid_opening_date = format_date(row.get('bid_opening_date'))
            bid_award_date = format_date(row.get('bid_award_date'))
            bid_notice_date = format_date(row.get('bid_notice_date'))
            contract_signing_date = format_date(row.get('contract_signing_date'))
            direct_commission_date = format_date(row.get('direct_commission_date'))
            quality_supervision_registration_date = format_date(row.get('quality_supervision_registration_date'))
            construction_permit_date = format_date(row.get('construction_permit_date'))
            construction_start_order_date = format_date(row.get('construction_start_order_date'))
            preliminary_acceptance_date = format_date(row.get('preliminary_acceptance_date'))
            archive_acceptance_date = format_date(row.get('archive_acceptance_date'))
            completion_acceptance_date = format_date(row.get('completion_acceptance_date'))
            entity_handover_date = format_date(row.get('entity_handover_date'))
            archive_handover_date = format_date(row.get('archive_handover_date'))
            completion_filing_date = format_date(row.get('completion_filing_date'))
            settlement_completion_date = format_date(row.get('settlement_completion_date'))
            final_account_completion_date = format_date(row.get('final_account_completion_date'))
            contract_start_date = format_date(row.get('contract_start_date'))
            contract_end_date = format_date(row.get('contract_end_date'))
            
            # 处理数值
            total_investment = format_number(row.get('total_investment'))
            direct_cost = format_number(row.get('direct_cost'))
            contract_duration = format_number(row.get('contract_duration'))
            contract_amount = format_number(row.get('contract_amount'))
            
            # 处理项目名称
            project_name = row.get('project_name')
            if pd.isna(project_name) or project_name is None or project_name == '':
                project_number = row.get('project_number')
                if not pd.isna(project_number) and project_number is not None:
                    project_name = generate_default_project_name(project_number, used_project_numbers)
            
            # 获取其他文本字段
            bidding_mode = row.get('bidding_mode')
            survey_design_unit = row.get('survey_design_unit')
            cost_compilation_unit = row.get('cost_compilation_unit')
            cost_audit_unit = row.get('cost_audit_unit')
            construction_unit = row.get('construction_unit')
            supervision_unit = row.get('supervision_unit')
            using_unit = row.get('using_unit')
            using_unit_name = row.get('using_unit_name')
            field_contact = row.get('field_contact')
            contact_phone = row.get('contact_phone')
            current_week_work = row.get('current_week_work')
            next_week_plan = row.get('next_week_plan')
            visual_progress = row.get('visual_progress')
            issues = row.get('issues')
            solutions = row.get('solutions')
            payment_progress = row.get('payment_progress')
            
            # 转义字符串
            project_code = escape_sql_string(row.get('project_code'))
            project_name = escape_sql_string(project_name)
            description = escape_sql_string(row.get('description'))
            location = escape_sql_string(row.get('location'))
            phase_status = escape_sql_string(row.get('phase_status'))
            task_document_no = escape_sql_string(row.get('task_document_no'))
            planning_type = escape_sql_string(row.get('planning_type'))
            bidding_mode = escape_sql_string(bidding_mode)
            survey_design_unit = escape_sql_string(survey_design_unit)
            cost_compilation_unit = escape_sql_string(cost_compilation_unit)
            cost_audit_unit = escape_sql_string(cost_audit_unit)
            construction_unit = escape_sql_string(construction_unit)
            supervision_unit = escape_sql_string(supervision_unit)
            using_unit = escape_sql_string(using_unit)
            using_unit_name = escape_sql_string(using_unit_name)
            field_contact = escape_sql_string(field_contact)
            contact_phone = escape_sql_string(contact_phone)
            current_week_work = escape_sql_string(current_week_work)
            next_week_plan = escape_sql_string(next_week_plan)
            visual_progress = escape_sql_string(visual_progress)
            issues = escape_sql_string(issues)
            solutions = escape_sql_string(solutions)
            payment_progress = escape_sql_string(payment_progress)
            notes = escape_sql_string(notes)
            
            # 构建SQL语句的各个部分
            fields = """project_number, project_code, name, description, location, 
    status, sub_status, progress, phase_status, task_document_no,
    planning_type, construction_type, total_investment, direct_cost,
    task_issue_date, required_completion_date, extended_completion_date,
    scheme_design_confirm_date, preliminary_design_confirm_date, construction_drawing_review_date,
    bill_compilation_date, bill_audit_date, construction_drawing_confirm_date,
    bidding_announcement_date, enterprise_registration_date, bid_opening_date,
    bid_award_date, bid_notice_date, contract_signing_date, direct_commission_date,
    quality_supervision_registration_date, construction_permit_date, construction_start_order_date,
    preliminary_acceptance_date, archive_acceptance_date, completion_acceptance_date,
    entity_handover_date, archive_handover_date, completion_filing_date,
    settlement_completion_date, final_account_completion_date,
    bidding_mode, contract_start_date, contract_end_date, contract_duration, contract_amount,
    survey_design_unit, cost_compilation_unit, cost_audit_unit,
    construction_unit, supervision_unit, using_unit, using_unit_name, supply_unit,
    field_contact, contact_phone, current_week_work, next_week_plan,
    visual_progress, issues, solutions, payment_progress,
    created_at, updated_at, notes"""
            
            # 处理各个字段的SQL值
            # 日期字段
            task_issue_date_sql = f"'{task_issue_date}'" if task_issue_date else 'NULL'
            required_completion_date_sql = f"'{required_completion_date}'" if required_completion_date else 'NULL'
            extended_completion_date_sql = f"'{extended_completion_date}'" if extended_completion_date else 'NULL'
            scheme_design_confirm_date_sql = f"'{scheme_design_confirm_date}'" if scheme_design_confirm_date else 'NULL'
            preliminary_design_confirm_date_sql = f"'{preliminary_design_confirm_date}'" if preliminary_design_confirm_date else 'NULL'
            construction_drawing_review_date_sql = f"'{construction_drawing_review_date}'" if construction_drawing_review_date else 'NULL'
            bill_compilation_date_sql = f"'{bill_compilation_date}'" if bill_compilation_date else 'NULL'
            bill_audit_date_sql = f"'{bill_audit_date}'" if bill_audit_date else 'NULL'
            construction_drawing_confirm_date_sql = f"'{construction_drawing_confirm_date}'" if construction_drawing_confirm_date else 'NULL'
            bidding_announcement_date_sql = f"'{bidding_announcement_date}'" if bidding_announcement_date else 'NULL'
            enterprise_registration_date_sql = f"'{enterprise_registration_date}'" if enterprise_registration_date else 'NULL'
            bid_opening_date_sql = f"'{bid_opening_date}'" if bid_opening_date else 'NULL'
            bid_award_date_sql = f"'{bid_award_date}'" if bid_award_date else 'NULL'
            bid_notice_date_sql = f"'{bid_notice_date}'" if bid_notice_date else 'NULL'
            contract_signing_date_sql = f"'{contract_signing_date}'" if contract_signing_date else 'NULL'
            direct_commission_date_sql = f"'{direct_commission_date}'" if direct_commission_date else 'NULL'
            quality_supervision_registration_date_sql = f"'{quality_supervision_registration_date}'" if quality_supervision_registration_date else 'NULL'
            construction_permit_date_sql = f"'{construction_permit_date}'" if construction_permit_date else 'NULL'
            construction_start_order_date_sql = f"'{construction_start_order_date}'" if construction_start_order_date else 'NULL'
            preliminary_acceptance_date_sql = f"'{preliminary_acceptance_date}'" if preliminary_acceptance_date else 'NULL'
            archive_acceptance_date_sql = f"'{archive_acceptance_date}'" if archive_acceptance_date else 'NULL'
            completion_acceptance_date_sql = f"'{completion_acceptance_date}'" if completion_acceptance_date else 'NULL'
            entity_handover_date_sql = f"'{entity_handover_date}'" if entity_handover_date else 'NULL'
            archive_handover_date_sql = f"'{archive_handover_date}'" if archive_handover_date else 'NULL'
            completion_filing_date_sql = f"'{completion_filing_date}'" if completion_filing_date else 'NULL'
            settlement_completion_date_sql = f"'{settlement_completion_date}'" if settlement_completion_date else 'NULL'
            final_account_completion_date_sql = f"'{final_account_completion_date}'" if final_account_completion_date else 'NULL'
            contract_start_date_sql = f"'{contract_start_date}'" if contract_start_date else 'NULL'
            contract_end_date_sql = f"'{contract_end_date}'" if contract_end_date else 'NULL'
            
            values = f"""    {row.get('project_number') if not pd.isna(row.get('project_number')) else 'NULL'},
    {f"'{project_code}'" if project_code is not None else 'NULL'},
    {f"'{project_name}'" if project_name is not None else 'NULL'},
    {f"'{description}'" if description is not None else 'NULL'},
    {f"'{location}'" if location is not None else 'NULL'},
    '{status}',
    {f"'{sub_status}'" if sub_status else 'NULL'},
    {0 if status.startswith('施工') else 'NULL'},
    {f"'{phase_status}'" if phase_status is not None else 'NULL'},
    {f"'{task_document_no}'" if task_document_no is not None else 'NULL'},
    {f"'{planning_type}'" if planning_type is not None else 'NULL'},
    {f"'{construction_type}'" if construction_type else 'NULL'},
    {total_investment if total_investment is not None else 'NULL'},
    {direct_cost if direct_cost is not None else 'NULL'},
    {task_issue_date_sql},
    {required_completion_date_sql},
    {extended_completion_date_sql},
    {scheme_design_confirm_date_sql},
    {preliminary_design_confirm_date_sql},
    {construction_drawing_review_date_sql},
    {bill_compilation_date_sql},
    {bill_audit_date_sql},
    {construction_drawing_confirm_date_sql},
    {bidding_announcement_date_sql},
    {enterprise_registration_date_sql},
    {bid_opening_date_sql},
    {bid_award_date_sql},
    {bid_notice_date_sql},
    {contract_signing_date_sql},
    {direct_commission_date_sql},
    {quality_supervision_registration_date_sql},
    {construction_permit_date_sql},
    {construction_start_order_date_sql},
    {preliminary_acceptance_date_sql},
    {archive_acceptance_date_sql},
    {completion_acceptance_date_sql},
    {entity_handover_date_sql},
    {archive_handover_date_sql},
    {completion_filing_date_sql},
    {settlement_completion_date_sql},
    {final_account_completion_date_sql},
    {f"'{bidding_mode}'" if bidding_mode is not None else 'NULL'},
    {contract_start_date_sql},
    {contract_end_date_sql},
    {contract_duration if contract_duration is not None else 'NULL'},
    {contract_amount if contract_amount is not None else 'NULL'},
    {f"'{survey_design_unit}'" if survey_design_unit is not None else 'NULL'},
    {f"'{cost_compilation_unit}'" if cost_compilation_unit is not None else 'NULL'},
    {f"'{cost_audit_unit}'" if cost_audit_unit is not None else 'NULL'},
    {f"'{construction_unit}'" if construction_unit is not None else 'NULL'},
    {f"'{supervision_unit}'" if supervision_unit is not None else 'NULL'},
    {f"'{using_unit}'" if using_unit is not None else 'NULL'},
    {f"'{using_unit_name}'" if using_unit_name is not None else 'NULL'},
    NULL,
    {f"'{field_contact}'" if field_contact is not None else 'NULL'},
    {f"'{contact_phone}'" if contact_phone is not None else 'NULL'},
    {f"'{current_week_work}'" if current_week_work is not None else 'NULL'},
    {f"'{next_week_plan}'" if next_week_plan is not None else 'NULL'},
    {f"'{visual_progress}'" if visual_progress is not None else 'NULL'},
    {f"'{issues}'" if issues is not None else 'NULL'},
    {f"'{solutions}'" if solutions is not None else 'NULL'},
    {f"'{payment_progress}'" if payment_progress is not None else 'NULL'},
    '{now}',
    '{now}',
    {f"'{notes}'" if notes else 'NULL'}"""
            
            # 组合SQL语句
            sql = f"INSERT INTO projects (\n{fields}\n) VALUES (\n{values}\n);"
            
            sql_statements.append(sql)
        except Exception as e:
            print(f"处理第 {row.get('project_number')} 行时出错: {str(e)}")
    
    return sql_statements

# 主函数
def main():
    # 读取Excel文件，跳过前3行，从第4行开始读取数据
    print("正在读取Excel文件...")
    df = pd.read_excel('客户文件/代建项目基础数据录入大表.xlsx', skiprows=3)
    
    # 重命名列，使用第3行作为列名
    column_names = {
        df.columns[0]: 'project_number',
        df.columns[1]: 'project_name',
        df.columns[2]: 'construction_type',
        df.columns[3]: 'project_code',
        df.columns[4]: 'description',
        df.columns[5]: 'planning_type',
        df.columns[6]: 'location',
        df.columns[7]: 'status',
        df.columns[8]: 'phase_status',
        df.columns[9]: 'task_document_no',
        df.columns[10]: 'total_investment',
        df.columns[11]: 'direct_cost',
        df.columns[12]: 'task_issue_date',
        df.columns[13]: 'required_completion_date',
        df.columns[14]: 'extended_completion_date',
        df.columns[15]: 'scheme_design_confirm_date',  # 方案设计确认时间
        df.columns[16]: 'preliminary_design_confirm_date',  # 初步设计确认时间
        df.columns[17]: 'construction_drawing_review_date',  # 完成施工图审查时间
        df.columns[18]: 'bill_compilation_date',  # 完成清单编制时间
        df.columns[19]: 'bill_audit_date',  # 完成清单审核时间
        df.columns[20]: 'construction_drawing_confirm_date',  # 施工图确认时间
        df.columns[21]: 'bidding_announcement_date',  # 企业邀请/招标公告发布时间
        df.columns[22]: 'enterprise_registration_date',  # 企业报名时间
        df.columns[23]: 'bid_opening_date',  # 开标时间
        df.columns[24]: 'bid_award_date',  # 定标时间
        df.columns[25]: 'bid_notice_date',  # 中标通知书发放时间
        df.columns[26]: 'contract_signing_date',  # 合同签订时间
        df.columns[27]: 'direct_commission_date',  # 直接委托时间
        df.columns[28]: 'quality_supervision_registration_date',  # 质监注册时间
        df.columns[29]: 'construction_permit_date',  # 施工许可证时间
        df.columns[30]: 'construction_start_order_date',  # 开工令时间
        df.columns[31]: 'preliminary_acceptance_date',  # 初步验收时间
        df.columns[32]: 'archive_acceptance_date',  # 档案验收时间
        df.columns[33]: 'completion_acceptance_date',  # 竣工验收时间
        df.columns[34]: 'entity_handover_date',  # 实体移交时间
        df.columns[35]: 'archive_handover_date',  # 档案移交时间
        df.columns[36]: 'completion_filing_date',  # 竣工备案时间
        df.columns[37]: 'settlement_completion_date',  # 完成结算时间
        df.columns[38]: 'final_account_completion_date',  # 完成决算时间
        df.columns[39]: 'bidding_mode',  # 发包模式
        df.columns[40]: 'contract_start_date',  # 合同开工日期
        df.columns[41]: 'contract_end_date',  # 合同竣工日期
        df.columns[42]: 'contract_duration',  # 合同工期
        df.columns[43]: 'contract_amount',  # 施工合同价
        df.columns[44]: 'survey_design_unit',  # 勘察设计单位
        df.columns[45]: 'cost_compilation_unit',  # 造价编制单位
        df.columns[46]: 'cost_audit_unit',  # 造价审核单位
        df.columns[47]: 'construction_unit',  # 施工单位
        df.columns[48]: 'supervision_unit',  # 监理单位
        df.columns[49]: 'using_unit',  # 中心及下属单位/代供单位
        df.columns[50]: 'using_unit_name',  # 使用单位名称
        df.columns[51]: 'field_contact',  # 现场联系人
        df.columns[52]: 'contact_phone',  # 联系电话
        df.columns[53]: 'current_week_work',  # 本周主要工作
        df.columns[54]: 'next_week_plan',  # 下周工作计划
        df.columns[55]: 'visual_progress',  # 形象进度
        df.columns[56]: 'issues',  # 矛盾问题
        df.columns[57]: 'solutions',  # 解决措施
        df.columns[58]: 'payment_progress'  # 工程款支付情况
    }
    
    df = df.rename(columns=column_names)
    
    # 处理任务书日期
    print("正在处理任务书日期...")
    df = process_task_dates(df)
    
    # 输出日期处理结果样例
    print("\n日期处理结果样例:")
    date_sample = df[['project_number', 'original_task_issue_date', 'task_issue_date', 
                      'original_required_completion_date', 'required_completion_date']].head(10)
    for _, row in date_sample.iterrows():
        print(f"项目 {row['project_number']}:")
        print(f"  任务书下达时间: {row['original_task_issue_date']} -> {row['task_issue_date']}")
        print(f"  要求完成时限: {row['original_required_completion_date']} -> {row['required_completion_date']}")
    
    # 检查并修复扩展完成日期的'nan'值
    print("\n检查并修复扩展完成日期的'nan'值...")
    nan_count = 0
    for idx, row in df.iterrows():
        if 'extended_completion_date' in df.columns:
            if isinstance(row['extended_completion_date'], str) and row['extended_completion_date'].lower() == 'nan':
                df.at[idx, 'extended_completion_date'] = None
                nan_count += 1
    print(f"已修复 {nan_count} 个'nan'值")
    
    # 生成SQL语句
    print("\n正在生成SQL语句...")
    sql_statements = generate_insert_sql(df)
    
    # 写入SQL文件前，直接替换所有'nan'为NULL
    print("处理SQL语句中的'nan'值...")
    for i in range(len(sql_statements)):
        sql_statements[i] = sql_statements[i].replace("'nan'", "NULL")
    
    # 写入SQL文件
    print("正在写入SQL文件...")
    with open('import_projects.sql', 'w', encoding='utf-8-sig') as f:
        f.write("-- 项目数据导入SQL\n")
        f.write("-- 生成时间: " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "\n\n")
        
        for sql in sql_statements:
            f.write(sql + "\n\n")
    
    print(f"SQL生成完成，共生成 {len(sql_statements)} 条插入语句。")
    print("SQL文件已保存为: import_projects.sql")
    
    # 显示前3条SQL语句预览
    print("\n前3条SQL语句预览:")
    for i, sql in enumerate(sql_statements[:3]):
        print(f"\n--- SQL {i+1} ---")
        print(sql)
    
    # 验证SQL语句完整性
    print("\n验证SQL语句完整性...")
    incomplete_count = 0
    for i, sql in enumerate(sql_statements):
        if not sql.strip().endswith(';'):
            print(f"第 {i+1} 条SQL语句不完整")
            incomplete_count += 1
    
    if incomplete_count == 0:
        print("所有SQL语句都是完整的")
    else:
        print(f"发现 {incomplete_count} 条不完整的SQL语句")

if __name__ == "__main__":
    main() 